﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spCleanupBackupHistory]
#-- Purpose:		To purge all activity history older than 2 months.
#--	Last Update:	07/06/2011
#--					For a complete history - please review comments in SVN
#-- Called By:		Scheduled Job
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spCleanupBackupHistory]
AS

SET NOCOUNT ON

--- Set the date filter
DECLARE @dt datetime, @sCommand varchar(8000)
SELECT @dt = DATEADD(Month, -2, GETDATE())

--- Purge History
EXEC msdb.dbo.sp_delete_backuphistory @dt
EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date = @dt
EXEC msdb.dbo.sp_maintplan_delete_log null, null, @dt

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[xp_slssqlmaint]') AND OBJECTPROPERTY(id,N'IsExtendedProc') = 1)
  BEGIN
	SELECT	@sCommand = 'EXEC master.dbo.xp_slssqlmaint N''-CleanHis 60DAYS  BKUP MAINTPLN JBS Activity CentralActivity PurgeInfo Logshipping Status -NO_OUTPUT '''
	IF LEN(@sCommand) > 0
		EXEC (@sCommand)
  END
SET NOCOUNT OFF
